﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using Task_Network_Center.General;
using System.Windows;
using System.Data;

namespace Task_Network_Center.DataBaseTable
{
    class DepartementTable
    {
        MySqlConnection conn = null;
        MySqlConnection conn2 = null;


        public object[,] ListDepartementToStringArray()
        {
            object[,] result = new object[3,getDepartementRowCount()];
            int i = 0;
            try
            {
                conn = GCGGeneralLib.DataBaseConnection.DbConnection();
                conn.Open();
                String query = "SELECT * FROM tableconfigdepartement";
                MySqlCommand cmd = new MySqlCommand(query,conn);
                MySqlDataReader dr = cmd.ExecuteReader();
                
                while(dr.Read())
                {
                    result[0, i] = dr.GetInt32(0);
                    result[1,i] = dr.GetString(1);
                    result[2, i] = i;
                    i++;
                }
                dr.Close();
                conn.Close();
                conn = null;
                
                return result;
            }
            catch(Exception e)
            {
                MessageBox.Show("Erreur TableDepartement - ListDepartementToStringArray: " + e);
                return null;
            }
        }

            private Int64 getDepartementRowCount()
            {
            
            try{
                conn = GCGGeneralLib.DataBaseConnection.DbConnection();
                
                String query = "SELECT COUNT(*) FROM tableconfigdepartement";
                MySqlCommand cmd = new MySqlCommand(query,conn);
                conn.Open();

                Int64 result = (Int64)cmd.ExecuteScalar();
                
                conn.Close();
                conn = null;
                
                return result;            
            }
            catch(Exception e){
                MessageBox.Show("Erreur TableDepartement - getDepartementRowCount: " + e);
                return -1;
            }
        }

            public DataTable QueryTableReadable(string query)
            {

                DataTable result = new DataTable();
                result.Columns.Add("id_config_departement");
                result.Columns.Add("departement");
                result.Columns.Add("description");
                string id = "", departement = "", description = "";
                

                try
                {
                    conn = GCGGeneralLib.DataBaseConnection.DbConnection();
                    conn.Open();
                    //String query = "SELECT * FROM tableconfigdepartement";
                    MySqlCommand cmd = new MySqlCommand(query, conn);
                    MySqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        if (!dr.IsDBNull(0))
                        {
                            id = dr.GetInt32(0).ToString();  
                        }
                        else
                        {
                            id = "";
                        }

                        if (!dr.IsDBNull(1))
                        {
                            departement = dr.GetString(1);  
                        }
                        else
                        {
                            departement = "";
                        }

                        if (!dr.IsDBNull(2))
                        {
                            description = dr.GetString(2);   
                        }
                        else
                        {
                            description = "";
                        }
                        

                        result.Rows.Add(new Object[] {id,departement,description});
                    }
                    dr.Close();
                    conn.Close();
                    conn = null;

                    return result;
                }
                catch (Exception e)
                {
                    MessageBox.Show("Erreur TableDepartement - QueryTableReadable: " + e);
                    return null;
                }
            }

            public bool InsertRow(string departement, string description)
            {
                String query = "INSERT INTO tableconfigdepartement (id_config_departement,departement,description) VALUE (@p1,@p2,@p3)";

                int id = departementID();

                conn = GCGGeneralLib.DataBaseConnection.DbConnection();
                MySqlCommand cmd = new MySqlCommand(query, conn);
                try
                {
                    conn.Open();
                    cmd.Parameters.AddWithValue("p1", id);
                    cmd.Parameters.AddWithValue("p2", departement);
                    cmd.Parameters.AddWithValue("p3", description);

      

                    cmd.ExecuteNonQuery();
                    conn.Close();
                    return true;
                }
                catch (Exception e)
                {
                    MessageBox.Show("Erreur DepartementTable - InsertRow: " + e);
                    return false;
                }

            }


            public bool UpdateRow(int id, string departement, string description)
            {
                String query = "UPDATE tableconfigdepartement SET departement = @p1, description = @p2 WHERE id_config_departement = @p3";
                conn = GCGGeneralLib.DataBaseConnection.DbConnection();
                MySqlCommand cmd = new MySqlCommand(query, conn);
                try
                {
                    conn.Open();
                    cmd.Parameters.AddWithValue("p3", id);
                    cmd.Parameters.AddWithValue("p1", departement);
                    cmd.Parameters.AddWithValue("p2", description);

                    cmd.ExecuteNonQuery();
                    conn.Close();
                    return true;

                }
                catch (Exception e)
                {

                    MessageBox.Show("Erreur TaskTable - UpdateRow: " + e);
                    return false;
                }

            }

            private int departementID()
            {

                conn2 = GCGGeneralLib.DataBaseConnection.DbConnection();
                String query = "SELECT LAST_INSERT_ID() FROM tableconfigdepartement";
                MySqlCommand cmd = conn2.CreateCommand();
                cmd.CommandText = query;
                try
                {

                    conn2.Open();
                    cmd.ExecuteScalar();
                    int count = Convert.ToInt32(cmd.LastInsertedId);
                    conn2.Close();
                    conn2 = null;
                    return count + 1;
                }
                catch (Exception e)
                {
                    MessageBox.Show("Erreur DepartementTable - departementID: " + e);
                    return -1;
                }

            }
        }
    }

